﻿using System;
using System.Data;
using VegetableSale.NET.Entities;
using VegetableSale.NET.Utils;

namespace VegetableSale.NET.Dao;

public class StockMapper
{
    public List<Stock> getStockList(QueryDTO queryDTO)
    {
        String sql = "";

        if (queryDTO.Keyword.Equals(""))
        {
            sql = "select * from stock;";
        }
        else
        {
            sql = "SELECT * FROM stock WHERE commid='" + queryDTO.Keyword + "';";
        }

        List<Stock> stocks = new List<Stock>();

        DataSet dateSet = MySqlHelper.GetDataSet(sql);
        DataTable dataTable = dateSet.Tables[0];

        foreach (DataRow row in dataTable.Rows)
        {
            uint skid = (uint) row[0];
            int commid = (int) row[1];
            double skmoney = (double) row[2];
            int sknumber = (int) row[3];
            string totalprice = (string) row[4];
            DateTime skdate = (DateTime) row[5];

            Stock stock = new Stock(skid, commid, skmoney, sknumber, totalprice, skdate);
            stocks.Add(stock);
        }

        return stocks;
    }

    public Stock getStockById(int skid)
    {
        string sql = "SELECT * FROM stock WHERE skid=" + skid + ";";

        Stock stock = new Stock();

        DataSet dateSet = MySqlHelper.GetDataSet(sql);
        DataTable dataTable = dateSet.Tables[0];

        foreach (DataRow row in dataTable.Rows)
        {
            uint Skid = (uint) row[0];
            int commid = (int) row[1];
            double skmoney = (double) row[2];
            int sknumber = (int) row[3];
            string totalprice = (string) row[4];
            DateTime skdate = (DateTime) row[5];

            stock.Skid = Skid;
            stock.Commid = commid;
            stock.Skmoney = skmoney;
            stock.Sknumber = sknumber;
            stock.Totalprice = totalprice;
            stock.Skdate = skdate;
        }
        return stock;
    }

    public void addStock(StockQuery stockQuery)
    {
        string totalprice = Convert.ToString(stockQuery.Skmoney * stockQuery.Sknumber);
        string NowTime = TimeTransferUtils.GetSqlServerTimeString();
        String sql = "INSERT INTO stock (commid,skmoney,sknumber,totalprice,skdate) VALUES (" + stockQuery.Commid +
                     "," + stockQuery.Skmoney + "," + stockQuery.Sknumber + ",'" + totalprice + "','" + NowTime + "');";

        MySqlHelper.ExecuteSql(sql);
    }

    public void deleteStock(int skid)
    {
        String sql = "DELETE FROM stock WHERE skid=" + skid + ";";

        MySqlHelper.ExecuteSql(sql);
    }

    public void updateStock(StockUpdateQuery stockUpdateQuery)
    {
        string totalprice = Convert.ToString(stockUpdateQuery.Skmoney * stockUpdateQuery.Sknumber) + ".0";
        string NowTime = TimeTransferUtils.GetSqlServerTimeString();

        String sql = "UPDATE stock SET commid=" + stockUpdateQuery.Commid + ",skmoney=" + stockUpdateQuery.Skmoney +
                     ",sknumber=" + stockUpdateQuery.Sknumber + ",totalprice='" + totalprice + "',skdate='" + NowTime +
                     "' WHERE skid=" + stockUpdateQuery.Skid + ";";

        MySqlHelper.ExecuteSql(sql);
    }

    public List<Object> getDeleteStock(int skid)
    {
        string sql = "SELECT commid,sknumber FROM stock WHERE skid=" + skid + ";";

        List<Object> returnList = new List<Object>();

        DataSet dateSet = MySqlHelper.GetDataSet(sql);
        DataTable dataTable = dateSet.Tables[0];

        foreach (DataRow row in dataTable.Rows)
        {
            int commid = (int) row[0];
            int sknumber = (int) row[1];

            returnList.Add(commid);
            returnList.Add(sknumber);
        }

        return returnList;
    }

    public void updateStockMount(int id, int newStockNumber, int newStockMoney)
    {
        string sql = "UPDATE stock SET sknumber=" + newStockNumber + ",totalprice=" + newStockMoney + " WHERE skid=" +id+";";

        MySqlHelper.ExecuteSql(sql);
    }
}